﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;
using BOHVote.Core;
using BOHVote.Entity;

namespace BOHVote.DAL
{
    public class ApplyProvide
    {
        Database db;
        public ApplyProvide()
        {
            db = DatabaseFactory.CreateDatabase();
        }

        //public DataSet getAllApply(string status)
        //{ 

        //}

        #region <<  Apply Operate  >>

        #region  << Select  >>

        public int getNomalApply(string strApplyPeriodID)
        {
           
            string strSQL = "SELECT COUNT(1) FROM V_Applyed va";
            if (!string.IsNullOrEmpty(strApplyPeriodID))
            {
                strSQL += " where ApplyPeriodID='" + strApplyPeriodID + "' ";
            }
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteScalar(db, dbcommand);
        }

        public int getEvaluationApply(string Type, string strApplyPeriodID)
        {
            //string strSQL = "SELECT COUNT(1) FROM ApplyEvaluation ae WHERE ae.EvaluationResult = '" + Type + "' ";
            string strSQL = "SELECT COUNT(1) FROM ApplyEvaluation ae INNER JOIN ApplyBase ab ON ae.applyID = ab.applyID WHERE ae.EvaluationResult =  '" + Type + "'  AND ab.ApplyPeriodID='" + strApplyPeriodID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteScalar(db, dbcommand);
        }

        public DataSet getApplyDataSetByIDCardAndPeriod(string IDCard, string ApplyPeriod)
        {
            string strSQL = "SELECT * FROM ApplyBase WHERE 1 =1 ";
            if (!string.IsNullOrEmpty(IDCard))
            {
                strSQL += " AND IDCard = '" + IDCard + "' ";
            }
            if (!string.IsNullOrEmpty(ApplyPeriod))
            {
                strSQL += " AND ApplyPeriodID = '" + ApplyPeriod + "' ";
            }
            strSQL += " ORDER BY ApplyID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getApplyDataSet(string ApplyStyle,string UnitID, string GovernID, string ApplyName, string ApplyType, string SpecialityID, string SubjectID, string JobRankID, string IDCard,string ApplyPeriod)
            {
                string strSQL = "SELECT  CASE WHEN LEN(IsNull(ApplyPPT,''))=0 THEN 'false' ELSE 'true' END AS ppt , * FROM  V_Applyed  WHERE 1=1 ";
                if (GovernID!="-1" && UnitID=="-1")
                {
                    strSQL += " AND GovernID = '" + GovernID + " ' ";
                }
                else if (UnitID != "-1")
                {
                    strSQL += "    AND UnitID = '" + UnitID + "' ";
                }

                if (ApplyStyle != "-1" && !String.IsNullOrEmpty(ApplyStyle))
                {
                    strSQL += "    AND ApplyStyle = '" + ApplyStyle + "' ";
                }
                
                if (!String.IsNullOrEmpty(ApplyName))
                {
                    strSQL += "    AND Apply_Name like '%" + ApplyName + "%' ";
                }

                if (!String.IsNullOrEmpty(IDCard))
                {
                    strSQL += "    AND IDCard like '%" + IDCard + "%' ";
                }
                
                if (SpecialityID != "-1")
                {
                    strSQL += "    AND SpecialityID = '" + SpecialityID + "' ";
                }
                if (JobRankID != "-1")
                {
                    strSQL += "    AND JobRankID = '" + JobRankID + "' ";
                }
                if (SubjectID != "-1")
                {
                    strSQL += "    AND SubjectID = '" + SubjectID + "' ";
                }
                if (ApplyType != "-1")
                {
                    strSQL += "    AND ApplyType = '" + ApplyType + "' ";
                }
                if (!string.IsNullOrEmpty(ApplyPeriod))
                {
                    strSQL += "    AND ApplyPeriodID = '" + ApplyPeriod + "' ";
                }
                strSQL += " ORDER BY ApplyPrintID ASC,JobRankID,SubjectID,SpecialityID ";

                DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

                return Utility.ExecuteDataSet(db, dbcommand);
            }

        public DataSet getApplyByID(string ApplyID, string ApplyPeriod, string unitID, string GovrenID)
        {
            string strSQL = "SELECT ab.*,jr.JobRankLevel FROM V_Applyed ab LEFT JOIN JobRank jr ON ab.JobRankID = jr.JobRankID WHERE 1=1 ";
            if (!string.IsNullOrEmpty(ApplyID))
            {
                strSQL += " AND ab.ApplyID = " + ApplyID;
            }
            if (!string.IsNullOrEmpty(ApplyPeriod))
            {
                strSQL += " AND ab.ApplyPeriodID = '" + ApplyPeriod + "'";
            }
            if (string.IsNullOrEmpty(unitID) && !string.IsNullOrEmpty(GovrenID))
            {
                strSQL += " AND ab.GovernID = '" + GovrenID + "' ";
            }
            if (!string.IsNullOrEmpty(unitID))
            {
                strSQL += "    AND ab.UnitID = '" + unitID + "' ";
            }

            strSQL += " ORDER BY ab.UnitID, jr.JobRankLevel DESC, ab.JobRankID ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getApplyID(string ApplyID)
        {
            string strSQL = "SELECT ab.*,jr.JobRankLevel FROM ApplyBase ab LEFT JOIN JobRank jr ON ab.JobRankID = jr.JobRankID WHERE 1=1 ";
            if (!string.IsNullOrEmpty(ApplyID))
            {
                strSQL += " AND ab.ApplyID = " + ApplyID;
            }
            
            strSQL += " ORDER BY ab.UnitID, jr.JobRankLevel DESC, ab.JobRankID ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getInterViewApplys(string UnitID, string GovernID, string ApplyStyle, string ApplyType, string SpecialityID, string SubjectID, string JobRankID, string ApplyPeriod)
            {
                string strSQL = "SELECT * FROM ApplyBase where ApplyID not IN(SELECT ApplyID from ApplyEvaluation) ";
                if (GovernID!="-1" && UnitID=="-1")
                {
                    strSQL += " AND GovernID = '" + GovernID + " ' ";
                }
                else if (UnitID != "-1")
                {
                    strSQL += "    AND UnitID = '" + UnitID + "' ";
                }

                if (ApplyStyle != "-1")
                {
                    strSQL += "    AND ApplyStyle = '" + ApplyStyle + "' ";
                }
                if (SpecialityID != "-1")
                {
                    strSQL += "    AND SpecialityID = '" + SpecialityID + "' ";
                }
                if (JobRankID != "-1")
                {
                    strSQL += "    AND JobRankID = '" + JobRankID + "' ";
                }
                if (SubjectID != "-1")
                {
                    strSQL += "    AND SubjectID = '" + SubjectID + "' ";
                }
                if (ApplyType != "-1")
                {
                    strSQL += "    AND ApplyType = '" + ApplyType + "' ";
                }
                if (!string.IsNullOrEmpty(ApplyPeriod))
                {
                    strSQL += "    AND ApplyPeriodID = '" + ApplyPeriod + "' ";
                }
                strSQL += " ORDER BY ApplyPrintID ASC,JobRankID,SubjectID,SpecialityID ";

                DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

                return Utility.ExecuteDataSet(db, dbcommand);
            }

        #endregion

        #region << Insert  >>

        private string ApplyInsert(Database db,ApplyBase ab)
                {
                    string strSQL = "INSERT INTO ApplyBase "
                                           + "(Apply_Name,ApplyMobile,UnitID,GovernID,UnitType,Gender,ApplyImage,[age],Birthday,EducationLevel,ApplyPeriodID,"
                                           + " IDCard,School,CurrentSpecialityID,CurrentJobRankID,ApplyType,SpecialityID,"
                                           + " JobRankID,Applyplight,SubjectID,JobDate,JobTerm,ConversionDate,FixedJob,ForeignLanguageName,ForeignLanguageResult,"
                                           + " PoliticsResult,JobResult,ComputerResult,GraduationDate,LastApplyDate,"
                                           + " SpecialityName,SpecialityLevel,SpecialitapproveUnit,SpecialitPersonalRank,SpecialitDateTime, "
                                           + " Status,AwardsName,AwardsLevel,AwardsRank,AwardsPersonalRank,AwardsDateTime,"
                                           + " SpecialityName1,SpecialityLevel1,SpecialitapproveUnit1,SpecialitPersonalRank1,SpecialitDateTime1, "
                                           + " AwardsName1,AwardsLevel1,AwardsRank1,AwardsPersonalRank1,AwardsDateTime1,"
                                           + " DataEnteredUserID,DataEnteredDate,ApplyStyle,ApplyPPT)"
                                           + " VALUES('" + ab.Apply_Name + "','" + ab.ApplyMobile + "','" + ab.UnitID + "','" + ab.GovernID + "','" + ab.UnitType + "','" + ab.Gender + "','" + ab.ApplyImage + "','" + ab.age + "','" + ab.Birthday + "','" + ab.EducationLevel + "','" + ab.ApplyPeriodID + "','" + ab.IDCard + "',"
                                           + "  '" + ab.School + "','" + ab.CurrentSpecialityID + "','" + ab.CurrentJobRankID + "','" + ab.ApplyType + "','" + ab.SpecialityID + "','" + ab.JobRankID + "','" + ab.Applyplight + "','" + ab.SubjectID + "','" + ab.JobDate + "'," + ab.JobTerm + ",'" + ab.ConversionDate + "',"
                                           + "  '" + ab.FixedJob + "','" + ab.ForeignLanguageName + "','" + ab.ForeignLanguageResult + "','" + ab.PoliticsResult + "','" + ab.JobResult + "','" + ab.ComputerResult + "','" + ab.GraduationDate + "',"
                                           + "   '" + ab.LastApplyDate + "','" + ab.SpecialityName + "','" + ab.SpecialityLevel + "','" + ab.SpecialitapproveUnit + "','" + ab.SpecialitPersonalRank + "','" + ab.SpecialitDateTime + "','" + ab.Status + "',"
                                           + "   '" + ab.AwardsName + "','" + ab.AwardsLevel + "','" + ab.AwardsRank + "','" + ab.AwardsPersonalRank + "','" + ab.AwardsDateTime + "',"
                                           + "   '" + ab.SpecialityName1 + "','" + ab.SpecialityLevel1 + "','" + ab.SpecialitapproveUnit1 + "','" + ab.SpecialitPersonalRank1 + "','" + ab.SpecialitDateTime1 + "',"
                                           + "   '" + ab.AwardsName1 + "','" + ab.AwardsLevel1 + "','" + ab.AwardsRank1+ "','" + ab.AwardsPersonalRank1 + "','" + ab.AwardsDateTime1 + "',"
                                           + "   '" + ab.DataEnteredUserID + "',getdate(),'" + ab.ApplyStyle + "','" + ab.ApplyPPT + "') SELECT @@IDENTITY AS SEQUENCE";

                    DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

                    return Convert.ToString(Utility.ExecuteScalar(db, dbcommand));
                }

        public int ApplySave(ApplyBase ab)
        {
            int returnValue = 0;
            using (ITransactionManager transactionManager = new TransactionManager())
            {
                try
                {
                    transactionManager.BeginTransaction();
                    Database database =this.db;// transactionManager != null ? transactionManager.Database : this.db;
                    string strApplyID = ApplyInsert(database, ab);
                    transactionManager.Commit();
                    if (strApplyID != "" && strApplyID != "0")
                    {
                        if (ab.SeqID1 != 0 && ab.JournalsNo1 != "" && ab.ThesisTitle1 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(strApplyID), ab.SeqID1, ab.JournalsNo1, ab.ThesisTitle1, ab.PagersPrint1, ab.PeriodicalNo1);
                        if (ab.SeqID2 != 0 && ab.JournalsNo2 != "" && ab.ThesisTitle2 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(strApplyID), ab.SeqID2, ab.JournalsNo2, ab.ThesisTitle2, ab.PagersPrint2, ab.PeriodicalNo2);
                        if (ab.SeqID3 != 0 && ab.JournalsNo3 != "" && ab.ThesisTitle3 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(strApplyID), ab.SeqID3, ab.JournalsNo3, ab.ThesisTitle3, ab.PagersPrint3, ab.PeriodicalNo3);
                        if (ab.SeqID4 != 0 && ab.JournalsNo4 != "" && ab.ThesisTitle4 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(strApplyID), ab.SeqID4, ab.JournalsNo4, ab.ThesisTitle4, ab.PagersPrint4, ab.PeriodicalNo4);

                        ApplyPublicityInsert(database, Convert.ToInt32(strApplyID), ab.startmonth, ab.startday, ab.endmonth, ab.endday, ab.address, ab.response);
                    }
                    else
                    {
                        transactionManager.Rollback();
                    }
                    
                }
                catch(Exception ex)
                {
                    //transactionManager.Rollback();
                }

                returnValue = 1;
            }
            return returnValue;
        }

        #endregion

        #region <<  Update  >>

        public int PPTUpdate(string ApplyID, string PPTPath)
        {
            string strSQL = "UPDATE ApplyBase SET ApplyPPT = '" + PPTPath + "' WHERE ApplyID = " + ApplyID ;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        private int ApplyUpdate(Database db, ApplyBase ab)
        {
            string strSQL = " UPDATE ApplyBase SET UnitID = '" + ab.UnitID + "', ApplyMobile='" + ab.ApplyMobile + "',GovernID = '" + ab.GovernID + "', Age = '" + ab.age + "',  ApplyPeriodID = '" + ab.ApplyPeriodID + "', Apply_Name = '" + ab.Apply_Name + "', Gender = '" + ab.Gender + "', ApplyImage = '" + ab.ApplyImage + "', Birthday = '" + ab.Birthday + "', EducationLevel = '" + ab.EducationLevel + "',"
                                  + " IDCard = '" + ab.IDCard + "',School = '" + ab.School + "',GraduationDate = '" + ab.GraduationDate + "',CurrentSpecialityID = '" + ab.CurrentSpecialityID + "',CurrentJobRankID = '" + ab.CurrentJobRankID + "',ApplyType = '" + ab.ApplyType + "',SpecialityID = '" + ab.SpecialityID + "',JobRankID = '" + ab.JobRankID + "',SubjectID = '" + ab.SubjectID + "',"
                                  + " JobDate = '" + ab.JobDate + "',ConversionDate = '" + ab.ConversionDate + "',JobTerm = '" + ab.JobTerm + "',FixedJob = '" + ab.FixedJob + "',ForeignLanguageName = '" + ab.ForeignLanguageName + "',ForeignLanguageResult = '" + ab.ForeignLanguageResult + "',PoliticsResult = '" + ab.PoliticsResult + "',JobResult = '" + ab.JobResult + "',"
                                  + " ComputerResult = '" + ab.ComputerResult + "',Applyplight = '" + ab.Applyplight + "',LastApplyDate = '" + ab.LastApplyDate + "',SpecialityName = '" + ab.SpecialityName + "',SpecialityLevel = '" + ab.SpecialityLevel + "',SpecialitapproveUnit = '" + ab.SpecialitapproveUnit + "',SpecialitPersonalRank = '" + ab.SpecialitPersonalRank + "',SpecialitDateTime = '" + ab.SpecialitDateTime + "',"
                                  + " [Status] = '" + ab.Status + "',AwardsName = '" + ab.AwardsName + "',AwardsLevel = '" + ab.AwardsLevel + "', AwardsRank = '" + ab.AwardsRank + "',AwardsPersonalRank = '" + ab.AwardsPersonalRank + "',AwardsDateTime = '" + ab.AwardsDateTime + "',DataChangedUserID = '" + ab.DataEnteredUserID + "',DatachangedDate = getdate() ,ApplyStyle='" + ab.ApplyStyle + "' ,ApplyPPT='" + ab.ApplyPPT + "',"
                                  + " SpecialityName1 = '" + ab.SpecialityName1 + "',SpecialityLevel1 = '" + ab.SpecialityLevel1 + "',SpecialitapproveUnit1 = '" + ab.SpecialitapproveUnit + "',SpecialitPersonalRank1 = '" + ab.SpecialitPersonalRank1 + "',SpecialitDateTime1 = '" + ab.SpecialitDateTime1 + "',"
                                  + " AwardsName1 = '" + ab.AwardsName1 + "',AwardsLevel1 = '" + ab.AwardsLevel1 + "', AwardsRank1 = '" + ab.AwardsRank1 + "',AwardsPersonalRank1 = '" + ab.AwardsPersonalRank1 + "',AwardsDateTime1 = '" + ab.AwardsDateTime1 + "'"
                                  + " WHERE ApplyID='" + ab.ApplyID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

         public int UpdateApply(ApplyBase ab)
        {
            int returnValue = 0;
            using (ITransactionManager transactionManager = new TransactionManager())
            {
                try
                {
                    transactionManager.BeginTransaction();
                    Database database = transactionManager != null ? transactionManager.Database : this.db;

                    if (ab.ApplyID != "" && ab.ApplyID != "0")
                    {
                        DeleteThesisByApplyID(database, ab.ApplyID);
                        ApplyUpdate(database, ab);
                        if (ab.SeqID1 != 0 && ab.JournalsNo1 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(ab.ApplyID), ab.SeqID1, ab.JournalsNo1, ab.ThesisTitle1, ab.PagersPrint1, ab.PeriodicalNo1);
                        if (ab.SeqID2 != 0 && ab.JournalsNo2 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(ab.ApplyID), ab.SeqID2, ab.JournalsNo2, ab.ThesisTitle2, ab.PagersPrint2, ab.PeriodicalNo2);
                        if (ab.SeqID3 != 0 && ab.JournalsNo3 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(ab.ApplyID), ab.SeqID3, ab.JournalsNo3, ab.ThesisTitle3, ab.PagersPrint3, ab.PeriodicalNo3);
                        if (ab.SeqID4 != 0 && ab.JournalsNo4 != "")
                            ApplyThesisInsert(database, Convert.ToInt32(ab.ApplyID), ab.SeqID4, ab.JournalsNo4, ab.ThesisTitle4, ab.PagersPrint4, ab.PeriodicalNo4);
                        DataSet dstPublicity = dstApplyPublicity(ab.ApplyID);
                        if(dstPublicity!=null&&dstPublicity.Tables.Count>0&&dstPublicity.Tables[0].Rows.Count>0)
                            ApplyPublicityUpdate(database, Convert.ToInt32(ab.ApplyID), ab.startmonth, ab.startday,ab.endmonth, ab.endday, ab.address, ab.response);
                        else
                            ApplyPublicityInsert(database, Convert.ToInt32(ab.ApplyID), ab.startmonth, ab.startday, ab.endmonth, ab.endday, ab.address, ab.response);
                    }
                    else
                    {
                        transactionManager.Rollback();
                    }
                    transactionManager.Commit();
                }
                catch
                {
                    transactionManager.Rollback();
                }

                returnValue = 1;
            }
            return returnValue;
        }


         public int UpdateApplyUnActived(string ApplyID)
         {
             string strSQL = "UPDATE ApplyBase SET Status ='D' WHERE ApplyID = "+ApplyID;
             DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

             return Utility.ExecuteNonQuery(db, dbcommand);
         }

        #endregion

        #region << Delete  >>

         public int DeleteApply(string ApplyID)
         {
             int returnValue = 0;
             using (ITransactionManager transactionManager = new TransactionManager())
             {
                 
                 try
                 {
                     transactionManager.BeginTransaction();
                     Database database = transactionManager != null ? transactionManager.Database : this.db;
                     string strSQL = "DELETE FROM APPLYBASE WHERE ApplyID = " + ApplyID;

                     DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

                     returnValue = Utility.ExecuteNonQuery(database, dbcommand);

                     DeleteInterview(ApplyID,database);

                     DeleteApplyPublicity(database, ApplyID);

                     DeleteThesisByApplyID(database, ApplyID);

                     transactionManager.Commit();
                 }
                 catch
                 {
                     transactionManager.Rollback();
                 }
             }

             return returnValue;
         }

        #endregion

        #endregion

        #region <<  Thesis Operate  >>

         private int ApplyThesisInsert(Database db, int ApplyID, int SeqID, string JournalsNo, string ThesisTitle, string PagersPrint, string PeriodicalNo)
                {
                    string strSQL = "INSERT INTO ApplyThesis(ApplyID,SeqID,JournalsNo,ThesisTitle,PagersPrint,PeriodicalNo)VALUES"
                                            + " ( " + ApplyID + "," + SeqID + ",'" + JournalsNo + "','" + ThesisTitle + "','" + PagersPrint + "','" + PeriodicalNo + "') ";
                    DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

                    return Utility.ExecuteNonQuery(db, dbcommand);
                }

         public int DeleteThesisByApplyID(Database dadtabase,string applyid)
                {
                    string strSQL = " DELETE FROM ApplyThesis WHERE ApplyID = " + applyid;
                    DbCommand dbcommand = dadtabase.GetSqlStringCommand(strSQL);

                    return Utility.ExecuteNonQuery(dadtabase, dbcommand);
                }

         public int DeleteThesisByApplyID(string ApplyID)
         {
             string strSQL = " DELETE FROM ApplyThesis WHERE ApplyID = " + ApplyID;
             DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

             return Utility.ExecuteNonQuery(db, dbcommand);
         }

        public DataSet dstThesis(string strApplyID)
        {
            string strSQL = " SELECT * FROM ApplyThesis WHERE ApplyID  = " + strApplyID;
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }
        #endregion

        #region <<  ApplyPublicity  >>
        private int ApplyPublicityInsert(Database db, int ApplyID, string PublicityStartMonth, string PublicityStartDay, string PublicityEndMonth, string PublicityEndDay, string PublicityAddress, string PublicityResponse)
        {
            string strSQL = "INSERT INTO ApplyPublicity(ApplyID,startmonth,startday,endmonth,endday,address,response)VALUES(" + ApplyID + ",'" + PublicityStartMonth + "', '" + PublicityStartDay + "', '" + PublicityEndMonth + "', '" + PublicityEndDay + "', '" + PublicityAddress + "', '" + PublicityResponse + "' )";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        private int ApplyPublicityUpdate(Database db, int ApplyID, string PublicityStartMonth, string PublicityStartDay, string PublicityEndMonth, string PublicityEndDay, string PublicityAddress, string PublicityResponse)
        {
            string strSQL = "UPDATE ApplyPublicity SET startmonth='" + PublicityStartMonth + "',startday='" + PublicityStartDay + "',endmonth='" + PublicityEndMonth + "',endday='" + PublicityEndDay + "',address='" + PublicityAddress + "',response='" + PublicityResponse + "' WHERE ApplyID = " + ApplyID + " ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public DataSet dstApplyPublicity(string strApplyID)
        {
            string strSQL = "SELECT * FROM ApplyPublicity WHERE ApplyID = " + strApplyID + " ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int DeleteApplyPublicity(Database db, string applyid)
        {
            string strSQL = "DELETE FROM ApplyPublicity WHERE ApplyID = " + applyid + " ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }
        #endregion

        #region <<  Apply Evaluation  >>
        public int CleanPrintNumber(string ApplyPeriod, string SubjectID)
        {
            string strSQL = "UPDATE ApplyBase SET ApplyprintID = '' WHERE SubjectID='" + SubjectID + "' AND ApplyPeriodID='" + ApplyPeriod + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public byte BuildPrintNumberSubject(string ApplyPeriod, string SubjectID)
        {
            byte returnValue = 0;
            string strSP = "sp_MakeApplyNumberSubject";
            DbCommand dbcommand = db.GetStoredProcCommand(strSP);
            db.AddInParameter(dbcommand, "@ApplyPeriodID", DbType.String, ApplyPeriod);
            db.AddInParameter(dbcommand, "@SubjectID", DbType.String, SubjectID);
            db.AddParameter(dbcommand, "@returnValue", DbType.Byte, ParameterDirection.ReturnValue, "", DataRowVersion.Default, null);
            db.ExecuteNonQuery(dbcommand);
            returnValue = Convert.ToByte(dbcommand.Parameters["@returnValue"].Value);

            return returnValue;
        }

        public byte BuildPrintNumber(string ApplyPeriod)
        {
            byte returnValue = 0;
            string strSP = "sp_MakeApplyNumber";
            DbCommand dbcommand = db.GetStoredProcCommand(strSP);
            db.AddInParameter(dbcommand, "@ApplyPeriodID", DbType.String, ApplyPeriod);
            db.AddParameter(dbcommand, "@returnValue", DbType.Byte, ParameterDirection.ReturnValue, "", DataRowVersion.Default, null);
            db.ExecuteNonQuery(dbcommand);
            returnValue = Convert.ToByte(dbcommand.Parameters["@returnValue"].Value);

            return returnValue;
        }

        public int CountUnBuildNumber(string ApplyPeriod,string SubjectID)
        {
            int returnValue = 0;
            string strC = string.Empty;
            if (!String.IsNullOrEmpty(SubjectID) && SubjectID!="-1")
                strC = " AND ab.SubjectID='" + SubjectID +"' ";
            string strSQL = "SELECT Count(1) FROM (SELECT * FROM ApplyBase where ApplyID not IN(SELECT ApplyID from ApplyEvaluation)) ab WHERE ab.ApplyPeriodID = '" + ApplyPeriod + "' " + strC + " AND (ab.ApplyPrintID IS  null or ab.ApplyPrintID = '') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);
            returnValue = (int)Utility.ExecuteScalar(db, dbcommand);
            return returnValue;
        }

        public DataSet getApplyEvaluation(string EvaluationResult,string ApplyID,string UnitID, string GovernID, string ApplyName,string IDCard,string ApplyPrintID, string ApplyType, string SpecialityID, string SubjectID, string JobRankID, string ApplyPeriod)
        {
            string strSQL = "SELECT AB.*,AE.EvaluationResult,AE.Remark  FROM ApplyBase AB LEFT JOIN ApplyEvaluation AE ON AB.ApplyID = AE.ApplyID WHERE  1=1 AND [Status] <> 'D' ";
            if (!string.IsNullOrEmpty(ApplyID))
            {
                strSQL += " AND AB.ApplyID='"+ApplyID+"' ";
            }
            if (!string.IsNullOrEmpty(ApplyPeriod))
            {
                strSQL += " AND AB.ApplyPeriodID =  '" + ApplyPeriod + "' ";
            }
            if (GovernID != "-1" && UnitID == "-1")
            {
                strSQL += " AND AB.GovernID = '" + GovernID + " ' ";
            }
            else if (UnitID != "-1")
            {
                strSQL += "    AND AB.UnitID = '" + UnitID + "' ";
            }

            if (!String.IsNullOrEmpty(ApplyName))
            {
                strSQL += "    AND AB.Apply_Name like '%" + ApplyName + "%'";
            }

            if (!String.IsNullOrEmpty(IDCard))
            {
                strSQL += "     AND AB.IDCard = '" + IDCard + "' ";
            }
            if (!String.IsNullOrEmpty(ApplyPrintID))
            {
                strSQL += "     AND AB.ApplyPrintID = '" + ApplyPrintID + "' ";
            }
            if (SpecialityID != "-1")
            {
                strSQL += "    AND AB.SpecialityID = '" + SpecialityID + "' ";
            }
            if (JobRankID != "-1")
            {
                strSQL += "    AND AB.JobRankID = '" + JobRankID + "' ";
            }
            if (SubjectID != "-1")
            {
                strSQL += "    AND AB.SubjectID = '" + SubjectID + "' ";
            }
            if (ApplyType != "-1")
            {
                strSQL += "    AND AB.ApplyType = '" + ApplyType + "' ";
            }
            if (!String.IsNullOrEmpty(EvaluationResult))
            {
                if (EvaluationResult.ToUpper() == "N")
                    strSQL += " AND ab.ApplyID NOT IN (SELECT ApplyID FROM ApplyEvaluation)";
                else
                    strSQL += " AND AE.EvaluationResult ='" + EvaluationResult + "' ";
            }
            strSQL += " order by AE.EvaluationResult desc,AB.ApplyPrintID";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int InsertApplyEvalutation(string ApplyID, string EvaluationResult, string Remark, string UserID)
        {
            string strSQL = "INSERT INTO ApplyEvaluation(ApplyID,EvaluationResult,Remark,EvaluationDateTime,EvalUationUserID)VALUES('" + ApplyID + "','" + EvaluationResult + "','" + Remark + "',getdate(),'" + UserID + "') ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int DeleteApplyEvaluaton(string ApplyID, string UserID)
        {
            string strSQL = "Delete FROM ApplyEvaluation WHERE ApplyID = '" + ApplyID + "' AND EvalUationUserID = '" + UserID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateApplyEvaluation(string ApplyID, string EvaluationResult, string Remark, string UserID)
        {
            string strSQL = "UPDATE ApplyEvaluation SET EvaluationResult = '" + EvaluationResult + "', Remark = '" + Remark + "', EvaluationDateTime =getdate(), EvalUationUserID ='" + UserID + "' WHERE ApplyID = '" + ApplyID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int CountApplyEvaluation(string ApplyID)
        {
            string strSQL = "SELECT COUNT(1) FROM ApplyEvaluation WHERE 1= 1  AND ApplyID = '" + ApplyID + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return (int)Utility.ExecuteScalar(db, dbcommand);
        }

        #endregion

        #region << Apply Interview >>
        public DataSet getApplyInterviewByPrintID(string ApplyPrintID)
        {
            string strSQL = "SELECT  ab.ApplyID,ab.Apply_Name, ab.age,ab.ApplyPeriodID,ab.Gender,ab.SubjectID,ab.ApplyPrintID,"
                                    + "                    ab.UnitType, ab.ApplyStyle, "
                                    + "                   ab.UnitID,ab.GovernID,ab.JobRankID,ab.ApplyType,ab.SpecialityID,ab.EducationLevel, "
                                    + "                   ai.InterViewID,ai.InterView_Month,ai.InterView_Day,ai.interview_TimeHH, "
                                    + "                   ai.interview_TimeMM,ai.ApplyPeriod  "
                                    + "    FROM  ApplyBase ab left join ApplyInterView ai on ab.ApplyID = ai.ApplyID WHERE 1=1 ";
            if (ApplyPrintID != "-1" && !string.IsNullOrEmpty(ApplyPrintID))
            {
                strSQL += " AND ab.ApplyPrintID = '" + ApplyPrintID + " ' ";
            }

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getApplyInterViewDataSet(string ApplyName,string ApplyPrintID,string UnitID, string GovernID, string ApplyStyle, string ApplyType, string SpecialityID, string SubjectID, string JobRankID, string ApplyPeriod)
        {
            string strSQL = "SELECT  ab.ApplyID,ab.Apply_Name, ab.age,ab.ApplyPeriodID,ab.Gender,ab.SubjectID,ab.ApplyPrintID,"
                                    + "                    ab.UnitType, ab.ApplyStyle,ab.Birthday,ab.CurrentJobRankID,ab.JobDate,ab.JobTerm, "
                                    + "                   ab.UnitID,ab.GovernID,ab.JobRankID,ab.ApplyType,ab.SpecialityID,ab.EducationLevel, "
                                    + "                   ai.InterViewID,ai.InterView_Month,ai.InterView_Day,ai.interview_TimeHH, "
                                    + "                   ai.interview_TimeMM,ai.ApplyPeriod  "
                                    + "    FROM  (SELECT * FROM ApplyBase where ApplyID not IN(SELECT ApplyID from ApplyEvaluation)) ab left join ApplyInterView ai on ab.ApplyID = ai.ApplyID WHERE 1=1 ";
            if (GovernID != "-1" && UnitID == "-1")
            {
                strSQL += " AND ab.GovernID = '" + GovernID + " ' ";
            }
            if (!String.IsNullOrEmpty(ApplyName))
            {
                strSQL += " AND ab.Apply_Name like '%" + ApplyName + "%' ";
            }
            else if (UnitID != "-1")
            {
                strSQL += "    AND ab.UnitID = '" + UnitID + "' ";
            }

            if (ApplyStyle != "-1")
            {
                strSQL += "    AND ab.ApplyStyle = '" + ApplyStyle + "' ";
            }
            if (SpecialityID != "-1")
            {
                strSQL += "    AND ab.SpecialityID = '" + SpecialityID + "' ";
            }
            if (JobRankID != "-1")
            {
                strSQL += "    AND ab.JobRankID = '" + JobRankID + "' ";
            }
            if (SubjectID != "-1")
            {
                strSQL += "    AND ab.SubjectID = '" + SubjectID + "' ";
            }
            if (ApplyType != "-1")
            {
                strSQL += "    AND ab.ApplyType = '" + ApplyType + "' ";
            }
            if (ApplyPrintID != "-1" && !string.IsNullOrEmpty(ApplyPrintID))
            {
                strSQL += "    AND ab.ApplyPrintID = '" + ApplyPrintID + "' ";
            }
            if (!string.IsNullOrEmpty(ApplyPeriod))
            {
                strSQL += "    AND ab.ApplyPeriodID = '" + ApplyPeriod + "' ";
            }
            string strOrderBy = string.Empty;
            if (GovernID != "-1")
                strOrderBy = " ab.UnitID,";
            strSQL += " ORDER BY "+strOrderBy+" ab.ApplyPrintID ASC,ab.JobRankID,ab.SubjectID,ab.SpecialityID ";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public DataSet getApplyInteview(string ApplyID, string ApplyPeriod)
        {
            string strSQL = "SELECT  ab.ApplyID,ab.Apply_Name, ab.age,ab.GovernID,ab.UnitID,ab.ApplyPeriodID,ab.Gender,ab.SubjectID,ab.ApplyPrintID,"
                                    + "                    ab.UnitType, ab.ApplyStyle, "
                                    + "                   ab.UnitID,ab.GovernID,ab.JobRankID,ab.ApplyType,ab.SpecialityID,ab.EducationLevel, "
                                    + "                   ai.InterViewID,ai.InterView_Month,ai.InterView_Day,ai.interview_TimeHH, "
                                    + "                   ai.interview_TimeMM,ai.ApplyPeriod  "
                                    + "    FROM  ApplyBase ab left join ApplyInterView ai on ab.ApplyID = ai.ApplyID WHERE 1=1 ";
            if (!string.IsNullOrEmpty(ApplyID))
            {
                strSQL += " AND ab.ApplyID = " + ApplyID ;
            }
            if (!string.IsNullOrEmpty(ApplyPeriod))
            {
                strSQL += " AND ab.ApplyPeriodID = '" + ApplyPeriod + "'";
            }
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteDataSet(db, dbcommand);
        }

        public int saveInterview(string ApplyID, string ApplyPeriod, string Month, string day, string HH, string MM)
        {
            string strSQL = "INSERT INTO ApplyInterView(ApplyID,InterView_Month,InterView_Day,interview_TimeHH,interview_TimeMM,ApplyPeriod)VALUES(" + ApplyID + ",'" + Month + "','" + day + "','" + HH + "','" + MM + "','" + ApplyPeriod + "')";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateInterview(string ApplyID, string InterviewID, string ApplyPeriod, string Month, string day, string HH, string MM)
        {
            string strSQL = "UPDATE ApplyInterView SET InterView_Month = '" + Month + "',InterView_Day = '" + day + "',interview_TimeHH = '" + HH + "',interview_TimeMM='" + MM + "' WHERE 1=1 ";
            if(!string.IsNullOrEmpty(ApplyID))
                strSQL += " AND ApplyID = " + ApplyID ;
            if(!string.IsNullOrEmpty(InterviewID))
                strSQL += "  AND InterViewID =" + InterviewID ;
            strSQL += "  AND ApplyPeriod ='" + ApplyPeriod + "' ";
            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(db, dbcommand);
        }

        public int UpdateSingleInterView(int ApplyID, int InterviewID, string ApplyPeriod, string Month, string Day, string HH, string MM)
        {
            string strSP = "sp_UpdateSingleInterViewTime";
            DbCommand dbcommand = db.GetStoredProcCommand(strSP);
            db.AddInParameter(dbcommand, "@ApplyPeriod", DbType.String, ApplyPeriod);
            db.AddInParameter(dbcommand, "@InterviewMonth", DbType.String, Month);
            db.AddInParameter(dbcommand, "@interviewDay", DbType.String, Day);
            db.AddInParameter(dbcommand, "@interviewHH", DbType.String, HH);
            db.AddInParameter(dbcommand, "@interviewMM", DbType.String, MM);
            db.AddInParameter(dbcommand, "@ApplyID", DbType.Int32, ApplyID);
            db.AddInParameter(dbcommand, "@interViewID", DbType.Int32, InterviewID);
             return db.ExecuteNonQuery(dbcommand);
        }


        public byte setInterviewTime(string SubjectID,string JobRank, string ApplyPeriod, string Month, string Day, string HH, string MM)
        {
            byte returnValue = 0;
            string strSP = "sp_SetInterviewTime";
            DbCommand dbcommand = db.GetStoredProcCommand(strSP);
            db.AddInParameter(dbcommand, "@ApplyPeriodID", DbType.String, ApplyPeriod);
            db.AddInParameter(dbcommand, "@InterviewMonth", DbType.String, Month);
            db.AddInParameter(dbcommand, "@interviewDay", DbType.String, Day);
            db.AddInParameter(dbcommand, "@interviewHH", DbType.String, HH);
            db.AddInParameter(dbcommand, "@interviewMM", DbType.String, MM);
            db.AddInParameter(dbcommand, "@SubjectID", DbType.String, SubjectID);
            db.AddInParameter(dbcommand, "@JobRank", DbType.String, JobRank);

            db.AddParameter(dbcommand, "@ReturnValue", DbType.Byte, ParameterDirection.ReturnValue, "", DataRowVersion.Default, null);
            db.ExecuteNonQuery(dbcommand);
            returnValue = Convert.ToByte(dbcommand.Parameters["@ReturnValue"].Value);

            return returnValue;
        }

        public int DeleteInterview(string ApplyID, Database database)
        {
            string strSQL = " DELETE FROM ApplyInterview WHERE ApplyID = " + ApplyID;
            DbCommand dbcommand = database.GetSqlStringCommand(strSQL);

            return Utility.ExecuteNonQuery(database, dbcommand);
        }

        #endregion

        public int countApplyByPeriod(string Period, string UnitID, string GovernID)
        {
            string strSQL = " SELECT Count(1) FROM ApplyBase WHERE 1=1 ";
            if(!string.IsNullOrEmpty(Period))
                strSQL  += "  AND ApplyPeriodID='" + Period + "'";
            if(!string.IsNullOrEmpty(UnitID))
                strSQL += "  AND UnitID='" + UnitID + "'";
            if (!string.IsNullOrEmpty(GovernID))
                strSQL += "  AND GovernID='" + GovernID + "'";

            DbCommand dbcommand = db.GetSqlStringCommand(strSQL);

            return Convert.ToInt32(Utility.ExecuteScalar(db, dbcommand));
        }

        
    }
}
